SELECT T.* ,
(SELECT COUNT(1)
FROM C1_NAVIGATION s
WHERE NOW() BETWEEN IFNULL(s.BEGIN_DATE, NOW()) AND IFNULL(s.END_DATE, NOW())
AND s.PARENT_ID = t.id
) AS childTotal
FROM C1_NAVIGATION T
JOIN c1_site t_site
ON (t_site.id = t.SITE_ID)
JOIN c1_dictionary t_dictionary
ON ((NOW() BETWEEN IFNULL(t_dictionary.BEGIN_DATE, NOW()) AND IFNULL(t_dictionary.END_DATE, NOW()))
AND t_dictionary.id = t.type_id )
WHERE (NOW() BETWEEN IFNULL(T.BEGIN_DATE, NOW()) AND IFNULL(T.END_DATE, NOW()))
AND (NOW() BETWEEN IFNULL(t_site.BEGIN_DATE, NOW()) AND IFNULL(t_site.END_DATE, NOW()))
AND t_site.code = :siteCode
AND t_dictionary.code = :typeCode
AND t.parent_id <#if (parentId??)>= :parentId<#else>IS NULL</#if>
ORDER BY t.ORDER_NUM